# server/app.py
from datetime import datetime
from apscheduler.schedulers.background import BackgroundScheduler
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS
import pandas as pd
import os
from dotenv import load_dotenv
from sms_sender import send_sms
from werkzeug.utils import secure_filename
#引用盐值加密密码
import bcrypt
# 新增正则表达式库用于邮箱格式校验
import re
# 导入 SQLAlchemy 的 IntegrityError 异常类
from sqlalchemy.exc import IntegrityError
# SQLAlchemy 用于生成 SQL 函数的工具
from sqlalchemy import func
# 新增JWT相关导入
from flask_jwt_extended import JWTManager, create_access_token, get_jwt_identity, jwt_required
import base64
import requests
from server import config
#在吐槽圈发布图片防伪造的方法中使用
from urllib.parse import urlparse


# 加载环境变量
load_dotenv()

app = Flask(__name__)
# 加载二级server中的config配置
app.config.from_object('server.config.Config')
CORS(app)  # 允许所有跨域请求
# ============== JWT配置新增开始 ==============
app.config["JWT_SECRET_KEY"] = os.getenv('JWT_SECRET', 'super-secret-key')  # 从环境变量获取密钥
#app.config["JWT_ACCESS_TOKEN_EXPIRES"] = 3600  # Token有效期1小时（秒）
# 禁用过期时间（需谨慎，仅用于演示需求）
app.config["JWT_ACCESS_TOKEN_EXPIRES"] = False  # 覆盖之前的3600配置
jwt = JWTManager(app)  # 初始化JWT扩展
# ============== JWT配置新增结束 ==============
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['ALLOWED_EXTENSIONS'] = {'xlsx', 'xls'}
app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_NAME')}"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# app.config['ALLOWED_IMAGE_EXTENSIONS'] = {'png', 'jpg', 'jpeg', 'gif'}
# app.config['MAX_IMAGE_SIZE'] = 10 * 1024 * 1024  # 10MB

db = SQLAlchemy(app)
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)



# 定义数据模型
class Customer(db.Model):
    __tablename__ = 'customer'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    expire_date = db.Column(db.Date, nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    notified = db.Column(db.Boolean, default=False)  # 对应tinyint(1)
    employee_number = db.Column(db.String(20), nullable=False)
    sms_reminded = db.Column(db.Boolean, default=False) 

def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']

#上传文本POST接口
@app.route('/api/upload', methods=['POST'])
def upload_file():
    # 获取前端传递的员工号
    employee_number = request.form.get('employee_number')  # 从form-data获取
    
    # 验证员工号
    if not employee_number or not employee_number.strip():
        return jsonify({
            "status": "error",
            "message": "请提供员工编号"
        }), 400
    
    # 检查文件上传
    if 'excel' not in request.files:
        return jsonify({"status": "error", "message": "未上传文件"}), 400
    
    file = request.files['excel']
    if file.filename == '':
        return jsonify({"status": "error", "message": "文件名为空"}), 400
    
    # 检查文件类型
    if not allowed_file(file.filename):
        return jsonify({"status": "error", "message": "仅支持.xlsx或.xls文件"}), 400
    
    try:
        # 创建上传目录
        os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
        
        # 保存文件
        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(file_path)
        
        # 解析Excel
        df = pd.read_excel(file_path)  # 从保存的文件读取
        
        # 验证列名
        required_columns = {'姓名', '手机号', '到期日期', '存款金额'}
        if not required_columns.issubset(df.columns):
            missing = required_columns - set(df.columns)
            return jsonify({
                "status": "error",
                "message": f"缺少必要列：{', '.join(missing)}"
            }), 400
        
        # 处理数据
        records = []
        for _, row in df.iterrows():
            records.append(Customer(
                employee_number=employee_number[:20],  # 添加员工号，限制长度防止SQL注入
                name=str(row['姓名']),
                phone=str(row['手机号']),
                expire_date=pd.to_datetime(row['到期日期']).date(),
                amount=float(row['存款金额']),
                notified=False  # 显式设置默认值
            ))
        
        # 批量插入
        db.session.bulk_save_objects(records)
        db.session.commit()
        
        return jsonify({
            "status": "success",
            "count": len(df),
            "message": f"成功插入 {len(df)} 条记录"
        })
    
    except pd.errors.ParserError as e:
        return jsonify({
            "status": "error",
            "message": "文件解析失败，请检查Excel格式"
        }), 400
    
    except ValueError as e:
        db.session.rollback()
        return jsonify({
            "status": "error",
            "message": f"数据格式错误：{str(e)}"
        }), 400
    
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"上传失败：{str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": f"服务器内部错误：{str(e)}"
        }), 500




# 新增Employee数据模型
class Employee(db.Model):
    __tablename__ = 'employees'
    employee_id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 自增主键
    employee_number = db.Column(db.String(20), unique=True, nullable=False)    # 必填且唯一
    name = db.Column(db.String(100), nullable=False)                           # 必填
    password = db.Column(db.String(255), nullable=False)                       # 必填
    department_id = db.Column(db.Integer)                                      # 可选
    job_title = db.Column(db.String(100))                                      # 可选
    hire_date = db.Column(db.Date, nullable=False)                             # 必填
    email = db.Column(db.String(100), unique=True, nullable=True, default=None)# 明确允许 NULL 可选但唯一
    organization_code = db.Column(db.String(9), nullable=True, comment='所在机构代码（9位数字）')


# 新增注册接口（添加到现有路由下方）
@app.route('/api/register', methods=['POST'])
def register_employee():
    try:
        data = request.get_json()
        
        # --- 输入验证阶段 ---
        # 必填字段校验
        required_fields = ['employee_number', 'name', 'hire_date', 'password']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少必要字段"}), 400
        
        # 密码复杂度校验（至少8位，包含字母和数字）
        if len(data['password']) < 8 or \
           not re.search(r"[A-Za-z]", data['password']) or \
           not re.search(r"\d", data['password']):
            return jsonify({"status": "error", "message": "密码需至少8位且包含字母和数字"}), 400
        
        # --- 唯一性检查阶段 ---
        # 检查员工编号是否重复
        if Employee.query.filter_by(employee_number=data['employee_number']).first():
            return jsonify({"status": "error", "message": "员工编号已存在"}), 409
        
        # --- 邮箱处理逻辑 ---
        email = data.get('email')
        
        # 情况 1：字段不存在或值为 null
        if email is None:
            email = None
        # 情况 2：字段存在但为空字符串
        elif isinstance(email, str):
            email = email.strip()  # 去除首尾空格
            email = email if email else None  # 空字符串转为 None
        
        # --- 邮箱唯一性检查（仅当 email 非空时）---
        if email:
            # 格式校验（正则加强版）
            if not re.match(r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$", email):
                return jsonify({"status": "error", "message": "邮箱格式无效"}), 400
            
            # 不区分大小写的唯一性检查
            existing = Employee.query.filter(
                func.lower(Employee.email) == func.lower(email)
            ).first()
            if existing:
                return jsonify({"status": "error", "message": "邮箱已被注册"}), 409
        
        
        # --- 数据处理阶段 ---
        # 密码哈希处理（自动包含盐）
        raw_password = data['password']
        hashed_password = bcrypt.hashpw(raw_password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
        
        # 覆盖明文密码内存（增强安全性）
        data['password'] = "overwritten_for_safety"
        del raw_password  # 主动删除引用
        
        # 日期解析
        hire_date = datetime.strptime(data['hire_date'], '%Y-%m-%d').date()
        
        # --- 数据入库阶段 ---
        new_employee = Employee(
            employee_number=data['employee_number'],
            name=data['name'],
            password=hashed_password,
            hire_date=hire_date,
            job_title=data.get('job_title'),
            department_id=data.get('department_id'),
            email=email
        )
        
        db.session.add(new_employee)
        db.session.commit()

        # ============== Token生成新增开始 ==============
        # 生成JWT访问令牌（使用员工ID作为身份标识）
        # 旧的token access_token = create_access_token(identity=new_employee.employee_id)
        access_token = create_access_token(identity=str(new_employee.employee_id))
        
        # 构建响应数据时添加token字段
        response_data = {
            "status": "success",
            "message": "注册成功",
            "data": {
                "employee_id": new_employee.employee_id,
                "employee_number": new_employee.employee_number,
                "name": new_employee.name,
                "token": access_token  # 新增token字段
            }
        }
        # ============== Token生成新增结束 ==============
        
        return jsonify(response_data), 201
        
    except ValueError as e:
        # 日期解析错误专属捕获
        return jsonify({"status": "error", "message": "日期格式错误，请使用YYYY-MM-DD格式"}), 400
    except IntegrityError as e:  # 捕获 SQLAlchemy 的 IntegrityError
        db.session.rollback()
        error_message = str(e.orig)
        if "Duplicate entry" in error_message:
            if "employee_number" in error_message:
                return jsonify({"status": "error", "message": "员工编号已存在"}), 409
            elif "email" in error_message:
                return jsonify({"status": "error", "message": "邮箱已被注册"}), 409
        return jsonify({"status": "error", "message": "数据冲突"}), 409
    except Exception as e:
        # 全局异常捕获（最后防线）
        db.session.rollback()
        app.logger.error(f"注册接口异常: {str(e)}")  # 记录日志但不要暴露敏感信息
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500

# ============== 登录接口新增开始 ==============
@app.route('/api/login', methods=['POST'])
def employee_login():
    try:
        data = request.get_json()

        # 必填字段校验
        required_fields = ['employee_number', 'name', 'password']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "员工编号、姓名和密码必须填写"}), 400

        # 根据员工编号查询用户
        employee = Employee.query.filter_by(employee_number=data['employee_number']).first()
        
        # 用户不存在情况
        if not employee:
            return jsonify({"status": "error", "message": "员工不存在"}), 404

        # 验证姓名一致性（严格匹配）
        if employee.name != data['name']:
            return jsonify({"status": "error", "message": "姓名与记录不符"}), 401

        # 验证密码正确性
        if not bcrypt.checkpw(data['password'].encode('utf-8'), employee.password.encode('utf-8')):
            return jsonify({"status": "error", "message": "密码错误"}), 401

        # 生成永久有效的Token（移除时效性）
        access_token = create_access_token(
            # 旧方法 identity=employee.employee_id,
            identity=str(employee.employee_id),  # 转为字符串
            expires_delta=None  # 设置为永不过期
        )

        # 构建响应数据
        response_data = {
            "status": "success",
            "message": "登录成功",
            "data": {
                "employee_id": employee.employee_id,
                "employee_number": employee.employee_number,
                "name": employee.name,
                "token": access_token
            }
        }

        return jsonify(response_data), 200

    except Exception as e:
        app.logger.error(f"登录接口异常: {str(e)}")
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500
# ============== 登录接口新增结束 ==============

# ============== 员工信息管理接口（整合GET/PATCH）开始 ==============
@app.route('/api/employee/update', methods=['GET', 'PATCH'])  # 同时支持GET和PATCH
@jwt_required()
def employee_info_management():
    try:
        # 公共逻辑：获取当前用户身份
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        
        if not employee:
            return jsonify({
                "status": "error", 
                "message": "用户不存在"
            }), 404

        # ============== GET 请求处理（新增）==============
        if request.method == 'GET':
            return jsonify({
                "status": "success",
                "data": {
                    "employee_id": employee.employee_id,
                    "employee_number": employee.employee_number,
                    "name": employee.name,
                    "department_id": employee.department_id,
                    "job_title": employee.job_title,
                    "organization_code": employee.organization_code,
                    "email": employee.email,
                    "hire_date": employee.hire_date.strftime('%Y-%m-%d')
                }
            }), 200

        # ============== PATCH 请求处理（原逻辑）==============
        if request.method == 'PATCH':
            data = request.get_json()

            # 处理姓名更新
            if 'name' in data:
                new_name = data['name'].strip()
                if not new_name:
                    return jsonify({
                        "status": "error",
                        "message": "姓名不能为空"
                    }), 400
                employee.name = new_name

            # 处理密码更新
            if 'password' in data:
                new_password = data['password'].strip()  # 去除首尾空格
                
                # 只有在新密码非空时进行校验和更新
                if new_password:  # ✅ 新增空值判断
                    # 复杂度校验
                    if len(new_password) < 8 or \
                       not re.search(r"[A-Za-z]", new_password) or \
                       not re.search(r"\d", new_password):
                        return jsonify({
                            "status": "error",
                            "message": "密码需至少8位且包含字母和数字"
                        }), 400
                    
                    # 哈希处理
                    hashed_pw = bcrypt.hashpw(new_password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
                    employee.password = hashed_pw
                else:
                    # 密码字段存在但为空时，跳过更新（保持原密码）
                    pass

            # 处理岗位更新
            if 'job_title' in data:
                new_job_title = data['job_title'].strip() if data['job_title'] else None
                if new_job_title and len(new_job_title) > 100:
                    return jsonify({
                        "status": "error",
                        "message": "岗位名称超过长度限制"
                    }), 400
                employee.job_title = new_job_title

            # 处理机构代码更新（9位数字校验）
            if 'organization_code' in data:
                org_code = data['organization_code']
                if org_code in [None, "", "null"]:
                    employee.organization_code = None
                else:
                    org_code = str(org_code).strip()
                    if not re.fullmatch(r'^\d{9}$', org_code):
                        return jsonify({
                            "status": "error",
                            "message": "机构代码必须为9位数字"
                        }), 400
                    employee.organization_code = org_code

            db.session.commit()
            
            return jsonify({
                "status": "success",
                "message": "信息更新成功",
                "data": {
                    "employee_number": employee.employee_number,
                    "updated_fields": list(data.keys())
                }
            }), 200

    except ValueError as e:
        return jsonify({
            "status": "error",
            "message": "参数类型错误"
        }), 400
    except IntegrityError as e:
        db.session.rollback()
        app.logger.error(f"数据库完整性错误: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "数据冲突，请检查唯一性约束"
        }), 409
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"接口异常: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
# ============== 员工信息管理接口（整合GET/PATCH）结束 ==============

# ============== 数据预览接口新增开始 ==============

@app.route('/api/customers', methods=['GET'])
def get_customers():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')    # get传值与post的区别要注意
        # employee_number = request.form.get('employee_number')  # 从form-data获取

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库
        customers = Customer.query.filter_by(
            employee_number=employee_number
        ).order_by(
            Customer.expire_date.desc()
        ).all()

        # 格式化结果
        result = []
        for customer in customers:
            result.append({
                "name": customer.name,
                "phone": customer.phone,
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount)  # 转换为float类型便于序列化
            })

        return jsonify({
            "status": "success",
            "count": len(result),
            "data": result
        }), 200

    except Exception as e:
        app.logger.error(f"查询客户数据失败: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# ============== 数据预览接口新增结束 ==============

# ============== 到期提醒预览接口新增开始 ==============
@app.route('/api/pending_sms_customers', methods=['GET'])
def get_pending_sms_customers():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库（新增sms_reminded过滤条件）
        customers = Customer.query.filter_by(
            employee_number=employee_number,
            sms_reminded=0  # 新增过滤条件
        ).order_by(
            Customer.expire_date.asc()  # 按到期日期升序排列（即将到期的在前）
        ).all()

        # 格式化结果
        result = []
        for customer in customers:
            result.append({
                "id": customer.id,  # 新增返回客户ID便于后续操作
                "name": customer.name,
                "phone": customer.phone,
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount),
                "sms_reminded": customer.sms_reminded  # 返回当前状态
            })

        return jsonify({
            "status": "success",
            "count": len(result),
            "data": result
        }), 200

    except Exception as e:
        app.logger.error(f"查询待提醒客户失败: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
# ============== 到期提醒预览接口新增结束 ==============

# ============== 到期提醒回调更新sms_reminded接口新增开始 ==============
@app.route('/api/update_sms_status', methods=['POST'])
def update_sms_status():
    try:
        data = request.get_json()
        
        # 必填字段校验
        required_fields = ['customer_id', 'employee_number']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少客户ID或员工编号"}), 400
        
        # 查询客户记录（确保员工号匹配）
        customer = Customer.query.filter_by(
            id=data['customer_id'],
            employee_number=data['employee_number']
        ).first()
        
        if not customer:
            return jsonify({"status": "error", "message": "未找到匹配的客户记录或权限不足"}), 404
        
        # 更新短信提醒状态
        customer.sms_reminded = 1
        db.session.commit()
        
        return jsonify({
            "status": "success",
            "message": "短信状态更新成功",
            "data": {
                "customer_id": customer.id,
                "sms_reminded": customer.sms_reminded
            }
        }), 200
    
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"更新短信状态失败: {str(e)}", exc_info=True)
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500

# ============== 到期提醒回调更新sms_reminded接口新增结束 ==============

# ============== 客户金额汇总接口新增开始 ==============
@app.route('/api/client_summary', methods=['GET'])
def get_client_summary():
    try:
        # 获取员工编号参数
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not employee_number:
            return jsonify({
                "status": "error",
                "message": "缺少员工编号参数"
            }), 400

        # 查询数据库并聚合数据
        results = db.session.query(
            Customer.name,
            Customer.phone,
            func.sum(Customer.amount).label('total_amount')
        ).filter_by(
            employee_number=employee_number
        ).group_by(
            Customer.name,
            Customer.phone
        ).order_by(
            func.sum(Customer.amount).desc()
        ).all()

        # 格式化结果
        summary_list = [{
            "name": name,
            "phone": phone,
            "total_amount": float(total_amount)
        } for name, phone, total_amount in results]

        # 构建响应数据（包含排名和完整列表）
        response_data = {
            "status": "success",
            "data": {
                # 前3名作为排行榜
                "ranking": summary_list[:3],
                # 完整列表（已排序）
                "full_list": summary_list
            }
        }

        return jsonify(response_data), 200

    except Exception as e:
        app.logger.error(f"获取客户汇总失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
# ============== 客户金额汇总接口新增结束 ==============

# ============== 搜索客户信息接口新增开始 ==============

@app.route('/api/search_customers', methods=['GET'])
def search_customers():
    try:
        # 获取查询参数
        name = request.args.get('name')
        employee_number = request.args.get('employee_number')

        # 参数校验
        if not name or not employee_number:
            return jsonify({
                "status": "error",
                "message": "姓名和员工编号为必填参数"
            }), 400

        # 查询数据库（模糊匹配姓名）
        customers = Customer.query.filter(
            Customer.employee_number == employee_number,
            Customer.name.like(f"%{name}%")
        ).order_by(
            Customer.expire_date.desc()
        ).all()

        # 按客户分组汇总数据
        result_dict = {}
        for customer in customers:
            key = (customer.name, customer.phone)
            if key not in result_dict:
                result_dict[key] = {
                    "name": customer.name,
                    "phone": customer.phone,
                    "total_amount": 0.0,
                    "deposits": []
                }
            
            deposit = {
                "expire_date": customer.expire_date.strftime('%Y-%m-%d'),
                "amount": float(customer.amount)
            }
            result_dict[key]["deposits"].append(deposit)
            result_dict[key]["total_amount"] += float(customer.amount)

        result_list = []
        for item in result_dict.values():
            item["total_amount"] = round(item["total_amount"], 2)
            item["deposits"].sort(key=lambda x: x["expire_date"], reverse=True)
            result_list.append(item)

        # ============== 新增消息提示逻辑 ==============
        if len(result_list) == 0:
            return jsonify({
                "status": "success",
                "message": "此客户记录不存在",
                "count": 0,
                "data": []
            }), 200
        else:
            return jsonify({
                "status": "success",
                "message": f"找到 {len(result_list)} 条客户记录",
                "count": len(result_list),
                "data": result_list
            }), 200
        # ============== 新增逻辑结束 ==============

    except Exception as e:
        app.logger.error(f"客户搜索失败: {str(e)}", exc_info=True)
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500
# ============== 搜索客户信息接口新增结束 ==============

# ============== 重要提醒接口新增开始 =================
# 新增提醒接口模型
class ImportantAlert(db.Model):
    __tablename__ = 'important_alerts'
    alert_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    employee_number = db.Column(db.String(20), db.ForeignKey('employees.employee_number'), nullable=False)
    alert_content = db.Column(db.Text, nullable=False)
    reminder_date = db.Column(db.DateTime, nullable=False)
    submitted_at = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    is_successful = db.Column(db.Boolean, default=False)
    device_info = db.Column(db.String(255))

@app.route('/api/create_alert', methods=['POST'])
def create_alert():
    try:
        # 从请求中获取柜员号
        data = request.get_json()
        employee_number = data.get('employee_number')
        
        # 验证柜员号是否存在
        if not employee_number:
            return jsonify({"status": "error", "message": "缺少柜员号"}), 400
        
        # 查询员工有效性
        employee = Employee.query.filter_by(employee_number=employee_number).first()
        if not employee:
            return jsonify({"status": "error", "message": "无效的柜员号"}), 404

        # 校验其他必填字段
        required_fields = ['alert_content', 'reminder_date']
        if not all(field in data for field in required_fields):
            return jsonify({"status": "error", "message": "缺少提醒内容或提醒时间"}), 400

        # 日期格式处理
        try:
            reminder_date = datetime.strptime(data['reminder_date'], '%Y-%m-%d %H:%M:%S')
        except ValueError:
            return jsonify({"status": "error", "message": "时间格式应为YYYY-MM-DD HH:MM:SS"}), 400

        # 创建提醒记录
        new_alert = ImportantAlert(
            employee_number=employee_number,
            alert_content=data['alert_content'],
            reminder_date=reminder_date,
            device_info=data.get('device_info', 'unknown')  # 可选设备信息
        )
        
        db.session.add(new_alert)
        db.session.commit()

        return jsonify({
            "status": "success",
            "message": "提醒创建成功",
            "data": {
                "alert_id": new_alert.alert_id,
                "reminder_time": new_alert.reminder_date.strftime('%Y-%m-%d %H:%M')
            }
        }), 201

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"创建提醒失败: {str(e)}", exc_info=True)
        return jsonify({"status": "error", "message": "服务器内部错误"}), 500

# ============== 重要提醒接口新增结束 =================


# 在Employee模型下方添加新数据模型
class AnonymousFeedback(db.Model):
    __tablename__ = 'anonymous_feedback'
    id = db.Column(db.Integer, primary_key=True)
    employee_number = db.Column(db.String(20), nullable=False)
    content = db.Column(db.String(500), nullable=False)
    images = db.Column(db.JSON)  # 使用SQLAlchemy的JSON类型
    like_count = db.Column(db.Integer, default=0)
    create_time = db.Column(db.DateTime, default=datetime.now)
    is_deleted = db.Column(db.Boolean, default=False)

class FeedbackLike(db.Model):
    __tablename__ = 'feedback_likes'
    id = db.Column(db.Integer, primary_key=True)
    feedback_id = db.Column(db.Integer, db.ForeignKey('anonymous_feedback.id'), nullable=False)
    employee_number = db.Column(db.String(20), nullable=False)
    create_time = db.Column(db.DateTime, default=datetime.now)

def allowed_file(filename):
    """修正后的文件类型验证方法"""
    allowed_extensions = app.config.get(
        'ALLOWED_IMAGE_EXTENSIONS', 
        {'png', 'jpg', 'jpeg', 'gif', 'webp'}  # 默认值
    )
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in allowed_extensions
# 在现有路由后添加新接口
# ============== 匿名吐槽接口开始 ==============
# 图片上传接口（图床）
@app.route('/api/upload/image', methods=['POST'])
@jwt_required()
def upload_image():
    try:
        # ================= 用户验证 =================
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404

        # ================= 文件接收 =================
        if 'image' not in request.files:
            return jsonify({"status": "error", "message": "请选择要上传的图片"}), 400
            
        uploaded_files = request.files.getlist('image')
        if len(uploaded_files) > 1:
            return jsonify({"status": "error", "message": "单次最多上传一张图片"}), 400

        uploaded_urls = []
        for file in uploaded_files:
            # ============= 文件验证 =============
            # 验证文件类型
            if not allowed_file(file.filename):
                return jsonify({
                    "status": "error",
                    "message": f"不支持的文件类型: {file.filename.split('.')[-1]} (支持类型: {', '.join(app.config['ALLOWED_IMAGE_EXTENSIONS'])})"
                }), 400

            # 验证文件大小 (配置读取)
            max_size = app.config.get('MAX_IMAGE_SIZE', 2*1024*1024)
            file.seek(0, os.SEEK_END)
            file_length = file.tell()
            if file_length > max_size:
                return jsonify({
                    "status": "error",
                    "message": f"图片大小超过{max_size//1024//1024}MB限制"
                }), 413
            file.seek(0)  # 重置文件指针

            # ============= 图床请求 =============
            try:
                # 构建符合EasyImages要求的参数
                upload_data = {
                    'token': app.config['EASYIMAGES_TOKEN'],
                    'api': 'upload'  # 关键参数：指定上传动作
                }
                upload_files = {
                    'image': (file.filename, file.stream, file.mimetype)
                }

                # 发送请求
                response = requests.post(
                    'https://EasyImages.10v.cc/api/index.php',
                    files=upload_files,
                    data=upload_data,
                    timeout=15
                )

                # ========= 响应处理 =========
                # 记录原始响应
                app.logger.debug(f"图床响应状态码: {response.status_code}")
                app.logger.debug(f"图床原始响应: {response.text}")

                # 关键调整1：优先检查JSON结构
                try:
                    result = response.json()
                except json.JSONDecodeError:
                    return jsonify({
                        "status": "error",
                        "message": "图床返回数据格式异常"
                    }), 502

                # 关键调整2：根据实际响应字段判断
                if result.get('code') == 200:  # 成功状态
                    # 关键调整3：多种URL字段尝试
                    image_url = (
                        result.get('data', {}).get('url') or  # 文档标准格式
                        result.get('url') or                   # 备用字段1
                        result.get('image_url')                # 备用字段2
                    )
                    
                    if image_url:
                        uploaded_urls.append(image_url)
                    else:
                        app.logger.error("图床URL字段缺失")
                        continue
                else:
                    error_msg = result.get('msg') or result.get('message') or '未知错误'
                    app.logger.error(f"图床错误: {error_msg}")
                    return jsonify({
                        "status": "error",
                        "message": f"图床返回错误: {error_msg} (代码: {result.get('code')})"
                    }), 502

            except requests.exceptions.RequestException as e:
                app.logger.error(f"图床连接异常: {str(e)}")
                return jsonify({
                    "status": "error",
                    "message": "图片服务暂时不可用"
                }), 503

        # ============ 返回结果 ============
        if not uploaded_urls:
            return jsonify({
                "status": "error",
                "message": "所有图片上传失败"
            }), 400

        return jsonify({
            "status": "success",
            "data": {
                "urls": uploaded_urls
            }
        }), 200

    except Exception as e:
        app.logger.error(f"上传处理异常: {str(e)}")
        return jsonify({
            "status": "error",
            "message": "服务器内部错误"
        }), 500

# 发布吐槽按钮（文字图床上传接口）

def validate_image_url(url):
    
    """严格验证图片URL合法性"""
    try:
        parsed = urlparse(url)
        
        # ============= 预处理小写 =============
        # 转换域名和路径为小写进行验证（不改变原始URL）
        lower_netloc = parsed.netloc.lower()
        lower_path = parsed.path.lower()
        
        # ============= 配置获取 =============
        valid_domains = app.config.get(
            'VALID_IMAGE_DOMAINS', 
            ['easyimages.10v.cc', 'your-cdn-domain.com']
        )
        # 配置域名统一小写处理
        lower_valid_domains = [d.lower() for d in valid_domains]
        
        # ============= 路径正则验证 =============
        # 使用小写路径进行匹配（根据实际图床路径结构调整正则）
        path_pattern = r'^/i/\d{4}/\d{2}/\d{2}/[a-z0-9_-]+\.(jpg|png|gif|webp)$'
        
        # ============= 综合验证条件 =============
        return (
            lower_netloc in lower_valid_domains and       # 域名校验（小写）
            parsed.scheme in ['http', 'https'] and        # 协议校验
            re.fullmatch(path_pattern, lower_path) and    # 路径校验（小写）
            bool(parsed.query) is False                   # 不允许带查询参数
        )
        
    except Exception as e:
        app.logger.error(f"URL验证异常: {str(e)}")
        return False

@app.route('/api/feedback', methods=['POST'])
@jwt_required()
def create_feedback():
    try:
        # ============== 用户身份验证 ==============
        current_user_id = int(get_jwt_identity())
        employee = Employee.query.get(current_user_id)
        if not employee:
            app.logger.warning(f"用户不存在: ID={current_user_id}")
            return jsonify({
                "status": "error",
                "code": "USER_NOT_FOUND",
                "message": "用户不存在"
            }), 404

        # ============== 数据完整性校验 ==============
        data = request.get_json()
        if not data:
            return jsonify({
                "status": "error",
                "code": "EMPTY_REQUEST",
                "message": "请求体不能为空"
            }), 400

        # 内容验证（允许空内容但不超过500字）
        content = data.get('content', '')
        if len(content) > 500:
            app.logger.info(f"内容过长: {len(content)}字符")
            return jsonify({
                "status": "error",
                "code": "CONTENT_TOO_LONG",
                "message": "内容超过500字限制"
            }), 400

        # 图片URL处理（字段名保持与上传接口一致）
        image_urls = data.get('urls', [])  # 注意字段名改为 urrs 与上传接口一致
        
        # 类型检查
        if not isinstance(image_urls, list):
            return jsonify({
                "status": "error",
                "code": "INVALID_IMAGE_URLS",
                "message": "图片参数格式错误"
            }), 400

        # 数量限制（根据需求调整）
        MAX_IMAGES = 1
        if len(image_urls) > MAX_IMAGES:
            return jsonify({
                "status": "error",
                "code": "TOO_MANY_IMAGES",
                "message": f"最多上传{MAX_IMAGES}张图片"
            }), 400

        # ============== 安全验证 ==============
        invalid_urls = [url for url in image_urls if not validate_image_url(url)]
        if invalid_urls:
            app.logger.warning(f"非法图片地址: {invalid_urls}")
            return jsonify({
                "status": "error",
                "code": "INVALID_IMAGE_URL",
                "message": f"检测到{len(invalid_urls)}个非法图片地址",
                "data": {
                    "invalid_urls": invalid_urls[:3]  # 防止泄露过多信息
                }
            }), 400

        # ============== 数据库操作 ==============
        try:
            new_feedback = AnonymousFeedback(
                employee_number=employee.employee_number,
                content=content.strip(),  # 去除首尾空白
                images=image_urls or None  # 空数组存为NULL
            )
            
            db.session.add(new_feedback)
            db.session.commit()
            
            app.logger.info(
                f"发布成功: 用户{employee.employee_number}, "
                f"内容长度{len(content)}, 图片数{len(image_urls)}"
            )

            return jsonify({
                "status": "success",
                "data": {
                    "id": new_feedback.id,
                    "create_time": new_feedback.create_time.isoformat(),
                    "image_count": len(image_urls)
                }
            }), 201

        except IntegrityError as e:
            db.session.rollback()
            app.logger.error(f"数据完整性错误: {str(e)}")
            return jsonify({
                "status": "error",
                "code": "DB_INTEGRITY_ERROR",
                "message": "数据存储失败"
            }), 500

    except json.JSONDecodeError:
        return jsonify({
            "status": "error",
            "code": "INVALID_JSON",
            "message": "非法的JSON格式"
        }), 400

    except Exception as e:
        db.session.rollback()
        app.logger.error(
            f"发布失败: {str(e)}\n"
            f"用户: {current_user_id}\n"
            f"内容: {content[:100]}...\n"
            f"图片: {image_urls}"
        )
        return jsonify({
            "status": "error",
            "code": "SERVER_ERROR",
            "message": "服务暂时不可用"
        }), 500


# 获取吐槽列表
@app.route('/api/feedbacks', methods=['GET'])
@jwt_required()
def get_feedbacks():
    try:
        
        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数
        
        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404
        
        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        page = request.args.get('page', 1, type=int)
        size = request.args.get('size', 10, type=int)
        
        # 获取轮播图（示例数据）
        banners = ["https://example.com/banner1.jpg", "https://example.com/banner2.jpg"]
        
        # 查询吐槽
        query = AnonymousFeedback.query.filter_by(is_deleted=False)
        pagination = query.order_by(AnonymousFeedback.create_time.desc()).paginate(
            page=page, per_page=size, error_out=False)
        
        feedbacks = []
        for item in pagination.items:
            # 检查是否是自己发布的
            is_mine = item.employee_number == current_employee_number
            
            # 检查是否已点赞
            is_liked = FeedbackLike.query.filter_by(
                feedback_id=item.id,
                employee_number=current_employee_number
            ).first() is not None
            
            feedbacks.append({
                "id": item.id,
                "content": item.content,
                "images": item.images,
                "like_count": item.like_count,
                "create_time": item.create_time.isoformat(),
                "is_mine": is_mine,
                "is_liked": is_liked
            })
        
        return jsonify({
            "status": "success",
            "data": {
                "banners": banners,
                "feedbacks": feedbacks,
                "pagination": {
                    "page": page,
                    "size": size,
                    "total": pagination.total
                }
            }
        })
        
    except Exception as e:
        app.logger.error(f"获取列表失败: {str(e)}")
        return jsonify({"status": "error", "message": "获取失败"}), 500

# 删除吐槽
@app.route('/api/feedback/<int:feedback_id>', methods=['DELETE'])
@jwt_required()
def delete_feedback(feedback_id):
    try:
        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数
        
        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404
        
        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        feedback = AnonymousFeedback.query.get(feedback_id)
        
        if not feedback or feedback.is_deleted:
            return jsonify({"status": "error", "message": "内容不存在"}), 404
        
        if feedback.employee_number != current_employee_number:
            # app.logger.debug(f"目标反馈用户: {feedback.employee_number}")
            return jsonify({"status": "error", "message": "无操作权限"}), 403
        
        feedback.is_deleted = True
        db.session.commit()
        
        return jsonify({"status": "success"})
    
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"删除失败: {str(e)}")
        return jsonify({"status": "error", "message": "删除失败"}), 500

# 点赞操作
@app.route('/api/feedback/<int:feedback_id>/like', methods=['POST'])
@jwt_required()
def toggle_like(feedback_id):
    try:
        # 获取身份并转为整数
        current_user_id = int(get_jwt_identity())  # 字符串转整数
        
        # 根据 employee_id 查询员工编号
        employee = Employee.query.get(current_user_id)
        if not employee:
            return jsonify({"status": "error", "message": "用户不存在"}), 404
        
        current_employee_number = employee.employee_number
        current_user = get_jwt_identity()
        action = request.json.get('action', 'add')
        
        feedback = AnonymousFeedback.query.get(feedback_id)
        if not feedback or feedback.is_deleted:
            return jsonify({"status": "error", "message": "内容不存在"}), 404
        
        existing_like = FeedbackLike.query.filter_by(
            feedback_id=feedback_id,
            employee_number=current_employee_number
        ).first()
        
        if action == 'add':
            if existing_like:
                return jsonify({"status": "error", "message": "已点赞"}), 400
                
            new_like = FeedbackLike(
                feedback_id=feedback_id,
                employee_number=current_employee_number
            )
            feedback.like_count += 1
            db.session.add(new_like)
            
        elif action == 'remove':
            if not existing_like:
                return jsonify({"status": "error", "message": "未点赞"}), 400
                
            feedback.like_count -= 1
            db.session.delete(existing_like)
            
        db.session.commit()
        return jsonify({
            "status": "success",
            "like_count": feedback.like_count
        })
        
    except Exception as e:
        db.session.rollback()
        app.logger.error(f"点赞操作失败: {str(e)}")
        return jsonify({"status": "error", "message": "操作失败"}), 500

# 轮播图片
# 轮播图片模型定义
class Banner(db.Model):
    __tablename__ = 'banners'
    id = db.Column(db.Integer, primary_key=True)
    image_url = db.Column(db.String(255), nullable=False)  # 图片地址
    sort_order = db.Column(db.Integer, default=0)          # 排序权重
    is_active = db.Column(db.Boolean, default=True)        # 是否启用
    create_time = db.Column(db.DateTime, default=datetime.now)

@app.route('/api/banners', methods=['GET'])
def get_banners():
    try:
        # 查询启用的轮播图并按排序权重降序
        banners = Banner.query.filter_by(is_active=True)\
                              .order_by(Banner.sort_order.desc())\
                              .all()
        
        # 提取图片URL
        banner_urls = [banner.image_url for banner in banners]
        
        return jsonify({
            "status": "success",
            "data": {
                "banners": banner_urls
            }
        })
        
    except Exception as e:
        app.logger.error(f"获取轮播图失败: {str(e)}")
        return jsonify({"status": "error", "message": "服务异常"}), 500
    
# ============== 匿名吐槽接口结束 ==============


# 定时任务：发送短信
def check_expired_customers():
    with app.app_context():
        today = datetime.now().date()
        customers = Customer.query.filter(
            Customer.expire_date == today,
            Customer.notified == False
        ).all()
        
        for customer in customers:
            # 示例：打印日志模拟发送短信
            print(f"[短信] 发送给 {customer.phone}: {customer.name}，您的存款{customer.amount}元已到期")
            customer.notified = True
            db.session.commit()
            
            ######测试发送短信######
            # 调用短信发送函数
            success = send_sms(
                phone=customer.phone,  # 需要从数据库或Excel中获取手机号字段
                name=customer.name,
                amount=customer.amount,
                expire_date=str(customer.expire_date)
            )
            if success:
                customer.notified = True
                db.session.commit()
                print(f"[成功] 短信已发送给 {customer.phone}")
            else:
                print(f"[失败] 短信发送失败 {customer.phone}")
            ######测试发送短信######

# 启动定时任务（每天9点执行）
scheduler = BackgroundScheduler()
# 临时修改定时任务为立即执行，测试时候执行
scheduler.add_job(check_expired_customers, 'date', run_date=datetime.now())
# 上线时执行定时任务（每天9点执行）
#scheduler.add_job(check_expired_customers, 'cron', hour=9)
scheduler.start()


if __name__ == '__main__':
    with app.app_context():
        db.create_all()  # 创建数据库表
    app.run(host='0.0.0.0', port=5001, debug=True)
